
cd

** PART 1: GET NE20

use "Comptroller_Panel.dta", clear

** estimates of interest earnings on securities (for pre-1926h2)

gen Interest_US=(sqrt(1+US_Yield)-1)*Invest_US
gen Interest_Muni=(sqrt(1+Muni_Yield)-1)*Invest_Municipal
gen Interest_Rail=(sqrt(1+Rail_Yield)-1)*Invest_Railroad
gen Interest_Public=(sqrt(1+Public_Yield)-1)*Invest_Public
egen EstimatedInterestXFed=rowtotal(Interest_*)

gen OtherInvestXFed=Invest_NonUS-Invest_Muni-Invest_Rail-Invest_Public-Invest_Fed

quietly reg Earn_Interest_IntOnInvestXFed EstimatedInterestXFed OtherInvestXFed if period<12 & location<1000, noconstant robust
predict Predict_Earn_SecuritiesXFed, xb

replace Predict_Earn_SecuritiesXFed=(A_USSecurities+A_NonUSSecuritiesXFed)*((1+Predict_Earn_SecuritiesXFed/(A_USSecurities+A_NonUSSecuritiesXFed))^2-1) if Annual==1

gen Interest_US_alt=(sqrt(1+US_Yield)-1)*A_USSecurities

quietly reg Earn_Interest_IntOnInvestXFed Interest_US_alt A_NonUSSecuritiesXFed if period<12 & location<1000, noconstant robust
predict Predict_Earn_SecuritiesXFed_alt, xb

replace Predict_Earn_SecuritiesXFed_alt=(A_USSecurities+A_NonUSSecuritiesXFed)*((1+Predict_Earn_SecuritiesXFed_alt/(A_USSecurities+A_NonUSSecuritiesXFed))^2-1) if Annual==1

replace Predict_Earn_SecuritiesXFed=Predict_Earn_SecuritiesXFed_alt if Predict_Earn_SecuritiesXFed==.
drop Predict_Earn_SecuritiesXFed_alt

** estimates of interest earnings on balances with other banks (for pre-1926h2)

gen ReturnOnInterbank=(1+Earn_Interest_BalancesWithBanks/A_DueFrom)^2-1

gen IsJune=0
replace IsJune=1 if month(date)==6

quietly xi: reg ReturnOnInterbank i.location|Rate_Interbank i.location|IsJune if period<12 & location<1000, noconstant robust
predict Predict_Return_Interbank, xb

gen Predict_Earn_Interbank=(sqrt(1+Predict_Return_Interbank)-1)*A_DueFrom
replace Predict_Earn_Interbank=Predict_Return_Interbank*A_DueFrom if Annual==1

** estimates of interest earnings on loans (for pre-1926h2)

gen Predict_Earn_Loans=Earn_InterestXFed-Predict_Earn_SecuritiesXFed-Predict_Earn_Interbank

** construct return on loans panel

gen ReturnOnLoans=(1+Earn_Interest_IntDisOnLoans/A_Loans)^2-1

gen Predict_Return_Loans=(1+Predict_Earn_Loans/A_Loans)^2-1
replace Predict_Return_Loans=Predict_Earn_Loans/A_Loans if Annual==1

quietly xi: reg ReturnOnLoans Predict_Return_Loans i.location*IsJune if location<1000, noconstant
predict Predict_Return_Loans_2, xb
replace Predict_Return_Loans=Predict_Return_Loans_2
drop Predict_Return_Loans_2

replace ReturnOnLoans=Predict_Return_Loans if ReturnOnLoans==. & location<1000

** construct return on securities panel

gen ReturnOnSecurities=(1+Earn_Interest_IntOnInvestXFed/(A_USSecurities+A_NonUSSecuritiesXFed))^2-1

gen Predict_Return_Securities=(1+Predict_Earn_SecuritiesXFed/(A_USSecurities+A_NonUSSecuritiesXFed))^2-1
replace Predict_Return_Securities=Predict_Earn_SecuritiesXFed/(A_USSecurities+A_NonUSSecuritiesXFed) if Annual==1

quietly xi: reg ReturnOnSecurities Predict_Return_Securities i.location*IsJune if location<1000, noconstant
predict Predict_Return_Securities_2, xb
replace Predict_Return_Securities=Predict_Return_Securities_2
drop Predict_Return_Securities_2

replace ReturnOnSecurities=Predict_Return_Securities if ReturnOnSecurities==. & location<1000

** elasticity regressions

keep location date district TimeWeightedRate ReturnOnLoans ReturnOnSecurities Exp_Interest InterestBearingLiabilities Annual

gen PaidOnDeposits=(1+Exp_Interest/InterestBearingLiabilities)^2-1
replace PaidOnDeposits=Exp_Interest/InterestBearingLiabilities if Annual==1

tsset location date
sort date location
replace ReturnOnLoans=0.1*ReturnOnLoans[_n]+0.9*ReturnOnLoans[_n-1] if location==153
replace ReturnOnSecurities=0.1*ReturnOnSecurities[_n]+0.9*ReturnOnSecurities[_n-1] if location==153
replace PaidOnDeposits=0.1*PaidOnDeposits[_n]+0.9*PaidOnDeposits[_n-1] if location==153
drop if location==152
gen log_ReturnOnLoans=log(ReturnOnLoans)
gen log_ReturnOnSecurities=log(ReturnOnSecurities)
gen log_TimeWeightedRate=log(TimeWeightedRate)
gen log_PaidOnDeposits=log(PaidOnDeposits)
save "temp.dta", replace

gen IsDec=0
replace IsDec=1 if month(date)==12
quietly statsby, by(location) clear: reg log_ReturnOnLoans log_TimeWeightedRate IsDec
drop _b_cons _b_IsDec
rename _b_log_TimeWeightedRate le20
save "Main.dta", replace

use "temp.dta", clear
quietly statsby, by(location) clear: reg log_ReturnOnSecurities log_TimeWeightedRate
drop _b_cons
rename _b_log_TimeWeightedRate se20
merge 1:1 location using "Main.dta"
drop _merge
gen ne20=se20-le20
save "Main.dta", replace

use "temp.dta", clear
gen IsDec=0
replace IsDec=1 if month(date)==12
quietly statsby, by(location) clear: reg log_ReturnOnLoans log_PaidOnDeposits IsDec
drop _b_cons _b_IsDec
rename _b_log_PaidOnDeposits le20d
merge 1:1 location using "Main.dta"
gen ne20d=se20-le20d
drop _merge le20d
save "Main.dta", replace

use "temp.dta", clear
collapse (mean) district, by(location)
merge 1:1 location using "Main.dta"
drop _merge

gen geo=""
replace geo="Alabama" if location==20
replace geo="Arkansas" if location==50
replace geo="California" if location==62
replace geo="Los Angeles" if location==63
replace geo="San Francisco" if location==65
replace geo="Colorado" if location==72
replace geo="Denver" if location==73
replace geo="Delaware" if location==90
replace geo="Washington DC" if location==100
replace geo="Florida" if location==111
replace geo="Jacksonville" if location==112
replace geo="Georgia" if location==120
replace geo="Idaho" if location==140
replace geo="Chicago" if location==153
replace geo="Peoria" if location==154
replace geo="Iowa" if location==174
replace geo="Sioux City" if location==178
replace geo="Kansas" if location==182
replace geo="Topeka" if location==184
replace geo="Wichita" if location==185
replace geo="Maine" if location==210
replace geo="Maryland" if location==221
replace geo="Baltimore" if location==222
replace geo="Massachusetts" if location==231
replace geo="Boston" if location==232
replace geo="Minnesota" if location==251
replace geo="Minneapolis" if location==252
replace geo="St Paul" if location==253
replace geo="Kansas City" if location==272
replace geo="St Joseph" if location==273
replace geo="St Louis" if location==274
replace geo="Montana" if location==280
replace geo="Nebraska" if location==291
replace geo="Lincoln" if location==292
replace geo="Omaha" if location==293
replace geo="Nevada" if location==300
replace geo="New Hampshire" if location==310
replace geo="New York" if location==344
replace geo="Brooklyn and Bronx" if location==346
replace geo="New York City" if location==348
replace geo="North Carolina" if location==350
replace geo="North Dakota" if location==360
replace geo="Ohio" if location==373
replace geo="Cincinnati" if location==374
replace geo="Columbus" if location==376
replace geo="Oklahoma City" if location==384
replace geo="Tulsa" if location==385
replace geo="Oregon" if location==390
replace geo="Philadelphia" if location==402
replace geo="Pittsburgh" if location==403
replace geo="Rhode Island" if location==410
replace geo="South Carolina" if location==420
replace geo="South Dakota" if location==430
replace geo="Nashville" if location==447
replace geo="Texas" if location==452
replace geo="Dallas" if location==453
replace geo="Fort Worth" if location==455
replace geo="Galveston" if location==456
replace geo="Houston" if location==457
replace geo="San Antonio" if location==458
replace geo="Waco" if location==459
replace geo="Utah" if location==462
replace geo="Salt Lake City" if location==464
replace geo="Vermont" if location==470
replace geo="Virginia" if location==480
replace geo="Washington" if location==503
replace geo="Seattle" if location==504
replace geo="Wyoming" if location==530
replace geo="Connecticut in 1" if location==1000
replace geo="Connecticut in 2" if location==2000
replace geo="New Jersey in 2" if location==2500
replace geo="Pennsylvania in 3" if location==3000
replace geo="New Jersey in 3" if location==3500
replace geo="Pennsylvania in 4" if location==4000
replace geo="West Virginia in 4" if location==4100
replace geo="Kentucky in 4" if location==4200
replace geo="West Virginia in 5" if location==5000
replace geo="Remainder of 6" if location==6000
replace geo="Remainder of 7" if location==7000
replace geo="Kentucky in 8" if location==8000
replace geo="Remainder of 8" if location==8100
replace geo="Remainder of 9" if location==9000
replace geo="Remainder of 10" if location==10000
replace geo="Arizona in 11" if location==11000
replace geo="Remainder of 11" if location==11100
replace geo="Arizona in 12" if location==12000

rename location geocode
rename geo location
order location district ne20 le20 se20 ne20d geocode
sort geocode
save "Main.dta", replace
erase temp.dta


** PART 2: aggregate county-level data

** census variables

use "Census_County.dta", clear
drop if State=="" | level>1 | totpop==.
merge 1:1 State County using "County_Mapping_Assigned.dta"

replace location=State if _merge==1

replace location="Connecticut in 1" if State=="Connecticut" & _merge==1
replace location="New Jersey in 2" if State=="New Jersey" & _merge==1
replace location="Pennsylvania in 3" if State=="Pennsylvania" & _merge==1
replace location="West Virginia in 5" if State=="West Virginia" & _merge==1
replace location="Kentucky in 8" if State=="Kentucky" & _merge==1
replace location="Arizona in 12" if State=="Arizona" & _merge==1

replace location="Remainder of 11" if State=="New Mexico" & _merge==1
replace location="Remainder of 10" if State=="Oklahoma" & _merge==1
replace location="Remainder of 7" if State=="Wisconsin" & _merge==1
replace location="Remainder of 7" if State=="Michigan" & _merge==1
replace location="Remainder of 7" if State=="Indiana" & _merge==1
replace location="Remainder of 7" if State=="Illinois" & _merge==1
replace location="Remainder of 8" if State=="Missouri" & _merge==1
replace location="Remainder of 8" if State=="Mississippi" & _merge==1
replace location="Remainder of 6" if State=="Tennessee" & _merge==1
replace location="Remainder of 6" if State=="Louisiana" & _merge==1

collapse (sum) totpop urb920 nwnptot tsch1617 tsch1820 m1844 f1844 t1620tot mfgestab mfgavear mfgvalad cropval farms areaac acres farmval area tothom ownfree, by(location)

gen log_area=log(area)
gen log_pop=log(totpop)
gen urban=urb920/totpop
gen nwnp=nwnptot/totpop
gen age1844=(m1844+f1844)/totpop
gen school1620=(tsch1617+tsch1820)/t1620tot
gen mfgest_pc=log(mfgestab/totpop)
gen mfgwork=mfgavear/totpop
gen log_mfgva=log(mfgvalad/mfgestab)
gen farms_pc=log(farms/totpop)
gen acres0=acres/areaac
gen log_avgcrop=log(cropval/farms)
gen log_avgvalue=log(farmval/farms)
gen homeownclr=ownfree/tothom

drop acres
rename acres0 acres
keep location log_area log_pop urban nwnp age1844 school1620 mfgest_pc mfgwork log_mfgva farms_pc acres log_avgcrop log_avgvalue homeownclr
merge 1:1 location using "Main.dta"
drop _merge
save "Main.dta", replace

** hhi variable

use "HHI_County.dta", clear
merge 1:1 State County using "County_Mapping_Assigned.dta"

replace location=State if _merge==1

replace location="Connecticut in 1" if State=="Connecticut" & _merge==1
replace location="New Jersey in 2" if State=="New Jersey" & _merge==1
replace location="Pennsylvania in 3" if State=="Pennsylvania" & _merge==1
replace location="West Virginia in 5" if State=="West Virginia" & _merge==1
replace location="Kentucky in 8" if State=="Kentucky" & _merge==1
replace location="Arizona in 12" if State=="Arizona" & _merge==1

replace location="Remainder of 11" if State=="New Mexico" & _merge==1
replace location="Remainder of 10" if State=="Oklahoma" & _merge==1
replace location="Remainder of 7" if State=="Wisconsin" & _merge==1
replace location="Remainder of 7" if State=="Michigan" & _merge==1
replace location="Remainder of 7" if State=="Indiana" & _merge==1
replace location="Remainder of 7" if State=="Illinois" & _merge==1
replace location="Remainder of 8" if State=="Missouri" & _merge==1
replace location="Remainder of 8" if State=="Mississippi" & _merge==1
replace location="Remainder of 6" if State=="Tennessee" & _merge==1
replace location="Remainder of 6" if State=="Louisiana" & _merge==1

sort location
by location: egen depsum=sum(Total_Deposits)
gen wgthhi=HHI*Total_Deposits/depsum

collapse (sum) wgthhi, by(location)
rename wgthhi hhi
merge 1:1 location using "Main.dta"
drop _merge
save "Main.dta", replace

** failures variables

use "Failure_County.dta", clear
collapse (sum) num_fail val_fail, by(State County)
merge 1:1 State County using "County_Mapping_Assigned.dta"

replace location=State if _merge==1

replace location="Connecticut in 1" if State=="Connecticut" & _merge==1
replace location="New Jersey in 2" if State=="New Jersey" & _merge==1
replace location="Pennsylvania in 3" if State=="Pennsylvania" & _merge==1
replace location="West Virginia in 5" if State=="West Virginia" & _merge==1
replace location="Kentucky in 8" if State=="Kentucky" & _merge==1
replace location="Arizona in 12" if State=="Arizona" & _merge==1

replace location="Remainder of 11" if State=="New Mexico" & _merge==1
replace location="Remainder of 10" if State=="Oklahoma" & _merge==1
replace location="Remainder of 7" if State=="Wisconsin" & _merge==1
replace location="Remainder of 7" if State=="Michigan" & _merge==1
replace location="Remainder of 7" if State=="Indiana" & _merge==1
replace location="Remainder of 7" if State=="Illinois" & _merge==1
replace location="Remainder of 8" if State=="Missouri" & _merge==1
replace location="Remainder of 8" if State=="Mississippi" & _merge==1
replace location="Remainder of 6" if State=="Tennessee" & _merge==1
replace location="Remainder of 6" if State=="Louisiana" & _merge==1

collapse (sum) num_fail val_fail, by(location)
merge 1:1 location using "Main.dta"
drop _merge
save "Main.dta", replace

** mergers variables

use "Merger_County.dta", clear
collapse (sum) num_consol val_consol, by(State County)
merge 1:1 State County using "County_Mapping_Assigned.dta"

replace location=State if _merge==1

replace location="Connecticut in 1" if State=="Connecticut" & _merge==1
replace location="New Jersey in 2" if State=="New Jersey" & _merge==1
replace location="Pennsylvania in 3" if State=="Pennsylvania" & _merge==1
replace location="West Virginia in 5" if State=="West Virginia" & _merge==1
replace location="Kentucky in 8" if State=="Kentucky" & _merge==1
replace location="Arizona in 12" if State=="Arizona" & _merge==1

replace location="Remainder of 11" if State=="New Mexico" & _merge==1
replace location="Remainder of 10" if State=="Oklahoma" & _merge==1
replace location="Remainder of 7" if State=="Wisconsin" & _merge==1
replace location="Remainder of 7" if State=="Michigan" & _merge==1
replace location="Remainder of 7" if State=="Indiana" & _merge==1
replace location="Remainder of 7" if State=="Illinois" & _merge==1
replace location="Remainder of 8" if State=="Missouri" & _merge==1
replace location="Remainder of 8" if State=="Mississippi" & _merge==1
replace location="Remainder of 6" if State=="Tennessee" & _merge==1
replace location="Remainder of 6" if State=="Louisiana" & _merge==1

collapse (sum) num_consol val_consol, by(location)
merge 1:1 location using "Main.dta"
drop _merge
save "Main.dta", replace

** suspensions variables

use "Suspend_County.dta", clear
drop if V1=="S"
merge 1:1 State County using "County_Mapping_Assigned.dta"

replace location=State if _merge==1

replace location="Connecticut in 1" if State=="Connecticut" & _merge==1
replace location="New Jersey in 2" if State=="New Jersey" & _merge==1
replace location="Pennsylvania in 3" if State=="Pennsylvania" & _merge==1
replace location="West Virginia in 5" if State=="West Virginia" & _merge==1
replace location="Kentucky in 8" if State=="Kentucky" & _merge==1
replace location="Arizona in 12" if State=="Arizona" & _merge==1

replace location="Remainder of 11" if State=="New Mexico" & _merge==1
replace location="Remainder of 10" if State=="Oklahoma" & _merge==1
replace location="Remainder of 7" if State=="Wisconsin" & _merge==1
replace location="Remainder of 7" if State=="Michigan" & _merge==1
replace location="Remainder of 7" if State=="Indiana" & _merge==1
replace location="Remainder of 7" if State=="Illinois" & _merge==1
replace location="Remainder of 8" if State=="Missouri" & _merge==1
replace location="Remainder of 8" if State=="Mississippi" & _merge==1
replace location="Remainder of 6" if State=="Tennessee" & _merge==1
replace location="Remainder of 6" if State=="Louisiana" & _merge==1

gen num_susp=V170+V171+V172
gen val_susp=V102+V103+V104
rename V153 ntl_n29
rename V85 ntl_d29

collapse (sum) num_susp val_susp ntl_n29 ntl_d29, by(location)
merge 1:1 location using "Main.dta"
drop _merge
save "Main.dta", replace

** retail variables

use "Retail_County.dta", clear
merge 1:1 State County using "County_Mapping_Assigned.dta"

replace location=State if _merge==1

replace location="Connecticut in 1" if State=="Connecticut" & _merge==1
replace location="New Jersey in 2" if State=="New Jersey" & _merge==1
replace location="Pennsylvania in 3" if State=="Pennsylvania" & _merge==1
replace location="West Virginia in 5" if State=="West Virginia" & _merge==1
replace location="Kentucky in 8" if State=="Kentucky" & _merge==1
replace location="Arizona in 12" if State=="Arizona" & _merge==1

replace location="Remainder of 11" if State=="New Mexico" & _merge==1
replace location="Remainder of 10" if State=="Oklahoma" & _merge==1
replace location="Remainder of 7" if State=="Wisconsin" & _merge==1
replace location="Remainder of 7" if State=="Michigan" & _merge==1
replace location="Remainder of 7" if State=="Indiana" & _merge==1
replace location="Remainder of 7" if State=="Illinois" & _merge==1
replace location="Remainder of 8" if State=="Missouri" & _merge==1
replace location="Remainder of 8" if State=="Mississippi" & _merge==1
replace location="Remainder of 6" if State=="Tennessee" & _merge==1
replace location="Remainder of 6" if State=="Louisiana" & _merge==1

collapse (sum) Retail1929 Retail1933 Retail1935, by(location)
merge 1:1 location using "Main.dta"
drop _merge
drop if Retail1929==0 | Retail1929==. | ne20==.

gen city=0
replace city=1 if location=="Baltimore" | location=="Boston" | location=="Chicago" | location=="Cincinnati" | location=="Columbus"
replace city=1 if location=="Dallas" | location=="Denver" | location=="Fort Worth" | location=="Galveston" | location=="Houston"
replace city=1 if location=="Jacksonville" | location=="Kansas City" | location=="Lincoln" | location=="Los Angeles" | location=="Minneapolis"
replace city=1 if location=="Nashville" | location=="Oklahoma City" | location=="Omaha" | location=="Peoria" | location=="Philadelphia"
replace city=1 if location=="Pittsburgh" | location=="Salt Lake City" | location=="San Antonio" | location=="San Francisco" | location=="Seattle"
replace city=1 if location=="Sioux City" | location=="St Joseph" | location=="St Louis" | location=="St Paul" | location=="Topeka"
replace city=1 if location=="Tulsa" | location=="Waco" | location=="Wichita"
save "Main.dta", replace
